package com.sqlcheck.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.sqlcheck.entity.SqlStatement;
import com.sqlcheck.entity.ScanResult;
import com.sqlcheck.repository.SqlStatementRepository;
import com.sqlcheck.repository.ScanResultRepository;
import com.sqlcheck.dto.request.SqlStatementQueryDTO;
import com.sqlcheck.dto.response.SqlStatisticsVO;
import com.sqlcheck.dto.response.SqlStatementVO;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;
import java.util.HashMap;
import java.util.stream.Collectors;
import com.sqlcheck.exception.BatchOperationFailedException;

@Slf4j
@Service
@RequiredArgsConstructor
public class SqlStatementService {

    private final SqlStatementRepository sqlStatementRepository;
    private final ScanResultRepository scanResultRepository;

    /**
     * 批量操作策略：不启用降级机制
     * 当批量操作失败时，直接抛出异常，不降级为单条操作
     */
    private static final boolean ENABLE_FALLBACK_ON_FAILURE = false;

    /**
     * 分页查询SQL语句清单
     */
    public IPage<SqlStatement> getStatements(SqlStatementQueryDTO query) {
        IPage<SqlStatement> page = new Page<>(query.getCurrent(), query.getSize());
        QueryWrapper<SqlStatement> wrapper = new QueryWrapper<>();

        // 任务ID筛选
        if (query.getTaskId() != null) {
            wrapper.eq("task_id", query.getTaskId());
        }

        // SQL类型筛选
        if (query.getSqlType() != null) {
            wrapper.eq("sql_type", query.getSqlType());
        }

        // DDL类型筛选(仅对DDL语句有效)
        if (query.getDdlType() != null) {
            wrapper.eq("ddl_type", query.getDdlType())
                    .eq("sql_type", "DDL"); // 确保是DDL语句
        }

        // Java注解类型筛选(仅对Java源码有效)
        if (query.getJavaAnnotationType() != null) {
            wrapper.eq("java_annotation_type", query.getJavaAnnotationType())
                    .in("source_type", "MYBATIS_ANNOTATION", "JPA_ANNOTATION");
        }

        // 来源类型筛选
        if (query.getSourceType() != null) {
            wrapper.eq("source_type", query.getSourceType());
        }

        // 表名筛选
        if (StringUtils.isNotBlank(query.getTableName())) {
            wrapper.and(w -> w.eq("primary_table", query.getTableName())
                    .or()
                    .like("affected_tables", query.getTableName()));
        }

        // 关键词搜索
        if (StringUtils.isNotBlank(query.getKeyword())) {
            wrapper.and(w -> w.like("sql_content", query.getKeyword())
                    .or()
                    .like("class_name", query.getKeyword())
                    .or()
                    .like("method_name", query.getKeyword()));
        }

        wrapper.orderByAsc("statement_number");
        return sqlStatementRepository.selectPage(page, wrapper);
    }

    /**
     * 根据任务ID和语句序号获取SQL语句
     */
    public SqlStatement getByTaskAndNumber(Long taskId, Integer statementNumber) {
        return sqlStatementRepository.selectByTaskAndNumber(taskId, statementNumber);
    }

    /**
     * 获取任务的SQL统计信息
     * 添加错误处理以防表不存在或枚举映射错误时系统崩溃
     */
    public SqlStatisticsVO getStatistics(Long taskId) {
        try {
            List<SqlStatement> statements = sqlStatementRepository.selectByTaskId(taskId);

            return SqlStatisticsVO.builder()
                    .totalStatements(statements.size())
                    .statementsByType(statements.stream()
                            .collect(Collectors.groupingBy(SqlStatement::getSqlType, Collectors.counting())))
                    .statementsBySource(statements.stream()
                            .collect(Collectors.groupingBy(SqlStatement::getSourceType, Collectors.counting())))
                    .ddlStatementsByType(statements.stream()
                            .filter(s -> s.getSqlType() == SqlStatement.SqlType.DDL && s.getDdlType() != null)
                            .collect(Collectors.groupingBy(SqlStatement::getDdlType, Collectors.counting())))
                    .affectedTables(statements.stream()
                            .filter(s -> s.getPrimaryTable() != null)
                            .map(SqlStatement::getPrimaryTable)
                            .distinct()
                            .collect(Collectors.toList()))
                    .build();
        } catch (Exception e) {
            // 检查是否是表不存在或枚举映射错误
            if (e.getMessage() != null && (e.getMessage().contains("doesn't exist") ||
                    e.getMessage().contains("No enum constant") ||
                    e.getMessage().contains("ResultMapException") ||
                    e.getMessage().contains("sql_statements") ||
                    e.getMessage().contains("DdlType.MYBATIS_XML"))) {
                log.warn("sql_statements表查询异常，返回空统计信息。taskId={}, error={}", taskId, e.getMessage());
                return SqlStatisticsVO.builder()
                        .totalStatements(0)
                        .statementsByType(Map.of())
                        .statementsBySource(Map.of())
                        .ddlStatementsByType(Map.of())
                        .affectedTables(List.of())
                        .build();
            }
            // 重新抛出其他异常
            throw e;
        }
    }

    /**
     * 根据表名查询SQL语句
     */
    public List<SqlStatement> getStatementsByTable(String tableName) {
        return sqlStatementRepository.selectByTableName(tableName);
    }

    /**
     * 获取任务的SQL语句数量
     * 添加错误处理以防表不存在或枚举映射错误时系统崩溃
     */
    public int getStatementCount(Long taskId) {
        try {
            return sqlStatementRepository.countByTaskId(taskId);
        } catch (Exception e) {
            // 检查是否是表不存在或枚举映射错误
            if (e.getMessage() != null && (e.getMessage().contains("doesn't exist") ||
                    e.getMessage().contains("No enum constant") ||
                    e.getMessage().contains("ResultMapException") ||
                    e.getMessage().contains("sql_statements") ||
                    e.getMessage().contains("DdlType.MYBATIS_XML"))) {
                log.warn("sql_statements表查询异常，返回语句数量0。taskId={}, error={}", taskId, e.getMessage());
                return 0;
            }
            // 重新抛出其他异常
            throw e;
        }
    }

    /**
     * 根据SQL类型和来源类型查询
     */
    public List<SqlStatement> getStatementsByTypes(Long taskId, SqlStatement.SqlType sqlType,
            SqlStatement.SourceType sourceType) {
        return sqlStatementRepository.selectByTaskIdAndTypes(taskId, sqlType, sourceType);
    }

    /**
     * 关键词搜索
     */
    public List<SqlStatement> searchStatements(Long taskId, String keyword) {
        return sqlStatementRepository.searchByTaskIdAndKeyword(taskId, keyword);
    }

    /**
     * 根据Java注解类型查询
     */
    public List<SqlStatement> getStatementsByJavaAnnotationType(Long taskId,
            SqlStatement.JavaAnnotationType annotationType) {
        return sqlStatementRepository.selectByTaskIdAndJavaAnnotationType(taskId, annotationType);
    }

    /**
     * 根据MyBatis XML元素类型查询
     */
    public List<SqlStatement> getStatementsByMybatisXmlElement(Long taskId, SqlStatement.MybatisXmlElement xmlElement) {
        return sqlStatementRepository.selectByTaskIdAndMybatisXmlElement(taskId, xmlElement);
    }

    /**
     * 根据任务ID查询所有SQL语句
     * 添加错误处理以防表不存在或枚举映射错误时系统崩溃
     */
    public List<SqlStatement> getStatementsByTask(Long taskId) {
        try {
            return sqlStatementRepository.selectByTaskId(taskId);
        } catch (Exception e) {
            // 检查是否是表不存在或枚举映射错误
            if (e.getMessage() != null && (e.getMessage().contains("doesn't exist") ||
                    e.getMessage().contains("No enum constant") ||
                    e.getMessage().contains("ResultMapException") ||
                    e.getMessage().contains("sql_statements") ||
                    e.getMessage().contains("DdlType.MYBATIS_XML"))) {
                log.warn("sql_statements表查询异常，可能是枚举映射错误，返回空列表。taskId={}, error={}", taskId, e.getMessage());
                return List.of();
            }
            // 重新抛出其他异常
            throw e;
        }
    }

    /**
     * 批量保存SQL语句
     * 注意：此方法在失败时直接抛出异常，不会降级为单条保存
     * 根据系统配置 ENABLE_FALLBACK_ON_FAILURE，当前设置为不启用降级机制
     */
    public void batchSaveStatements(List<SqlStatement> statements) {
        if (statements == null || statements.isEmpty()) {
            log.debug("SQL语句列表为空，跳过批量保存");
            return;
        }

        // 明确记录批量操作策略
        log.debug("批量保存策略：降级机制启用状态 = {}", ENABLE_FALLBACK_ON_FAILURE);

        try {
            int insertedCount = sqlStatementRepository.batchInsert(statements);
            log.info("批量保存{}条SQL语句完成，实际插入{}条", statements.size(), insertedCount);

            // 验证插入数量
            if (insertedCount != statements.size()) {
                throw new BatchOperationFailedException("SQL语句插入", statements.size(), insertedCount);
            }
        } catch (BatchOperationFailedException e) {
            // 重新抛出批量操作异常，不进行降级处理
            log.error("批量保存SQL语句失败，根据系统策略不进行单条降级处理。共{}条语句", statements.size(), e);
            throw e;
        } catch (Exception e) {
            log.error("批量保存SQL语句失败，根据系统策略不进行单条降级处理。共{}条语句", statements.size(), e);
            throw new BatchOperationFailedException("SQL语句插入", "数据库操作失败", e);
        }
    }

    /**
     * 清理指定任务的所有SQL语句记录（软删除）
     * 用于任务重试时避免唯一约束冲突
     */
    public void cleanupTaskStatements(Long taskId) {
        if (taskId == null) {
            log.warn("任务ID为空，跳过SQL语句清理");
            return;
        }

        try {
            int deletedCount = sqlStatementRepository.softDeleteByTaskId(taskId);
            log.info("清理任务SQL语句记录: taskId={}, 删除记录数={}", taskId, deletedCount);
        } catch (Exception e) {
            log.error("清理任务SQL语句记录失败: taskId={}", taskId, e);
            throw new RuntimeException("清理任务SQL语句记录失败: " + e.getMessage());
        }
    }

    /**
     * 更新SQL语句的提取元数据
     */
    public void updateExtractionMetadata(Long statementId, Map<String, Object> metadata) {
        String metadataJson = com.sqlcheck.util.JsonUtils.safeToJsonString(metadata);
        sqlStatementRepository.updateExtractionMetadata(statementId, metadataJson);
    }

    /**
     * 获取指定任务的SQL语句列表（包含问题统计）
     */
    public List<SqlStatementVO> getStatementsWithIssuesByTask(Long taskId) {
        // 1. 获取SQL语句列表
        List<SqlStatement> statements = getStatementsByTask(taskId);

        // 2. 获取该任务的所有扫描结果
        QueryWrapper<ScanResult> resultWrapper = new QueryWrapper<>();
        resultWrapper.eq("task_id", taskId);
        List<ScanResult> scanResults = scanResultRepository.selectList(resultWrapper);

        // 3. 按语句编号分组扫描结果
        Map<Integer, List<ScanResult>> issuesByStatementNumber = scanResults.stream()
                .filter(result -> result.getStatementNumber() != null)
                .collect(Collectors.groupingBy(ScanResult::getStatementNumber));

        // 4. 构建返回结果
        return statements.stream().map(stmt -> {
            List<ScanResult> statementIssues = issuesByStatementNumber.getOrDefault(stmt.getStatementNumber(),
                    List.of());

            // 计算问题统计
            int issueCount = statementIssues.size();
            boolean hasIssues = issueCount > 0;

            // 按严重程度分组
            Map<String, Integer> issuesBySeverity = new HashMap<>();
            if (hasIssues) {
                issuesBySeverity = statementIssues.stream()
                        .filter(issue -> issue.getSeverity() != null)
                        .collect(Collectors.groupingBy(
                                issue -> issue.getSeverity().name(),
                                Collectors.collectingAndThen(Collectors.counting(), Math::toIntExact)));
            }

            // 构建SqlStatementVO
            SqlStatementVO.SqlStatementVOBuilder builder = SqlStatementVO.builder()
                    .taskId(stmt.getTaskId())
                    .statementNumber(stmt.getStatementNumber())
                    .sqlContent(stmt.getContent())
                    .sqlType(stmt.getSqlType())
                    .sourceType(stmt.getSourceType())
                    .isNativeQuery(stmt.getIsNativeQuery())
                    .issueCount(issueCount)
                    .hasIssues(hasIssues)
                    .issuesBySeverity(issuesBySeverity);

            // 根据来源类型设置不同的字段
            if (stmt.getSourceType() == SqlStatement.SourceType.DDL_SCRIPT) {
                // DDL相关字段
                builder.ddlType(stmt.getDdlType())
                        .primaryTable(stmt.getPrimaryTable())
                        .affectedTables(stmt.getAffectedTables())
                        .databaseObjects(stmt.getDatabaseObjects());
            } else {
                // Git源码相关字段
                builder.sourceFile(stmt.getSourceFile())
                        .lineNumber(stmt.getLineNumber())
                        .className(stmt.getClassName())
                        .methodName(stmt.getMethodName())
                        .javaAnnotationType(stmt.getJavaAnnotationType())
                        .mybatisXmlElement(stmt.getMybatisXmlElement())
                        .gitUrl(buildGitUrl(stmt));
            }

            // 通用字段
            builder.extractionMetadata(stmt.getExtractionMetadata());

            return builder.build();
        }).collect(Collectors.toList());
    }

    /**
     * 构建Git URL（从SqlStatementController复制的逻辑）
     */
    private String buildGitUrl(SqlStatement statement) {
        // TODO: 这里需要注入ScanTaskService来获取任务配置
        // 为了简化，暂时返回null，后续可以优化
        return null;
    }
}